using System;
using System.Collections.Generic;
using System.Text;
using SqlTools.DataAccessLibrary;
using System.Data.SqlClient;
using System.Data;

namespace SqlTools.SampleDAL.Data
{
	public class DbCustomer : DbBase<CustomerPrimaryKey, Customer>
	{
		public DbCustomer()
			: base("daltest")
		{
		}

		public DbCustomer(string connectionStringName)
			: base(connectionStringName)
		{
		}

		#region Select
		public override Customer SelectByPK(CustomerPrimaryKey key)
		{
			Customer customer = null;
			using ( SqlConnection conn = new SqlConnection(_connectionString) )
			{
				SqlCommand cmd = new SqlCommand("daltest_Customer_SelectByPK", conn);
				cmd.CommandType = CommandType.StoredProcedure;

				cmd.Parameters.Add("@CustomerID", SqlDbType.Int).Value = key.CustomerID;

				conn.Open();

				SqlDataReader sdr = cmd.ExecuteReader();

				if ( sdr.Read() )
				{
					customer = Customer.CreateFromReader(sdr);
					customer._ConnectionStringName = _connectionStringName;
				}
				sdr.Close();

				conn.Close();

				// load base class
				DbUser dbUser = new DbUser(_connectionStringName);

				dbUser.SelectByPK(customer, customer.Key);
			}

			return customer;
		}

		public DbList<Customer> SelectByParentCustomerID(int parentCustomerID)
		{
			DbList<Customer> customers = new DbList<Customer>();
			using ( SqlConnection conn = new SqlConnection(_connectionString) )
			{
				SqlCommand cmd = new SqlCommand("daltest_Customer_SelectByParentCustomerID", conn);
				cmd.CommandType = CommandType.StoredProcedure;

				cmd.Parameters.Add("@ParentCustomerID", SqlDbType.Int).Value = parentCustomerID;

				conn.Open();

				SqlDataReader sdr = cmd.ExecuteReader();

				while ( sdr.Read() )
				{
					Customer customer = Customer.CreateFromReader(sdr);
					customer._ConnectionStringName = _connectionStringName;

					customers.Add(customer);
				}
				sdr.Close();

				conn.Close();

				// load base class
				DbUser dbUser = new DbUser(_connectionStringName);

				foreach ( Customer customer in customers )
				{
					dbUser.SelectByPK(customer, customer.Key);
				}
			}

			return customers;
		}
		#endregion

		#region Insert
		public override CustomerPrimaryKey Insert(Customer customer, SqlConnection conn, SqlTransaction trans)
		{
			customer._ConnectionStringName = _connectionStringName;

			bool destroyConn = false;

			if ( conn == null )
			{
				destroyConn = true;
				conn = new SqlConnection(_connectionString);
			}

			try
			{
				SqlCommand cmd = new SqlCommand("daltest_Customer_Insert", conn);
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Transaction = trans;

				if ( conn.State != ConnectionState.Open )
				{
					conn.Open();
				}

				// First, execute base class insert
				DbUser dbUser = new DbUser(_connectionStringName);
				UserPrimaryKey userKey = dbUser.Insert(customer, conn, trans);
				customer.Key.UserID = userKey.UserID;

				cmd.Parameters.Add("@CustomerID", SqlDbType.Int).Direction = ParameterDirection.Output;
				cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = customer.Key.UserID;
				cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50).Value = customer.FirstName;
				cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50).Value = customer.LastName;
				cmd.Parameters.Add("@EmailAddress", SqlDbType.VarChar, 255).Value = customer.EmailAddress;
				cmd.Parameters.Add("@PhoneNumber", SqlDbType.VarChar, 50).Value = customer.PhoneNumber;
				if ( customer.ParentCustomerID == 0 )
					cmd.Parameters.Add("@ParentCustomerID", SqlDbType.Int);
				else
					cmd.Parameters.Add("@ParentCustomerID", SqlDbType.Int).Value = customer.ParentCustomerID;

				cmd.ExecuteNonQuery();

				if ( cmd.Parameters["@CustomerID"].Value != DBNull.Value )
				{
					customer.Key.CustomerID = (int)cmd.Parameters["@CustomerID"].Value;
				}
				customer._UpdateState(ObjectState.None);
			}
			finally
			{
				if ( destroyConn && conn.State == ConnectionState.Open )
				{
					conn.Close();
				}

				if ( destroyConn )
				{
					conn.Dispose();
				}
			}

			return customer.Key;
		}
		#endregion

		#region Update
		public override int Update(Customer customer, SqlConnection conn, SqlTransaction trans)
		{
			customer._ConnectionStringName = _connectionStringName;

			bool destroyConn = false;

			if ( conn == null )
			{
				destroyConn = true;
				conn = new SqlConnection(_connectionString);
			}

			try
			{
				SqlCommand cmd = new SqlCommand("daltest_Customer_Insert", conn);
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Transaction = trans;

				if ( conn.State != ConnectionState.Open )
				{
					conn.Open();
				}

				// First, execute base class update
				DbUser dbUser = new DbUser(_connectionStringName);
				dbUser.Update(customer, conn, trans);

				cmd.Parameters.Add("@CustomerID", SqlDbType.Int).Value = customer.Key.CustomerID;
				cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = customer.Key.UserID;
				cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50).Value = customer.FirstName;
				cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50).Value = customer.LastName;
				cmd.Parameters.Add("@EmailAddress", SqlDbType.VarChar, 255).Value = customer.EmailAddress;
				cmd.Parameters.Add("@PhoneNumber", SqlDbType.VarChar, 50).Value = customer.PhoneNumber;
				if ( customer.ParentCustomerID == 0 )
					cmd.Parameters.Add("@ParentCustomerID", SqlDbType.Int);
				else
					cmd.Parameters.Add("@ParentCustomerID", SqlDbType.Int).Value = customer.ParentCustomerID;

				int rows = cmd.ExecuteNonQuery();

				customer._UpdateState(ObjectState.None);

				return rows;
			}
			finally
			{
				if ( destroyConn && conn.State == ConnectionState.Open )
				{
					conn.Close();
				}

				if ( destroyConn )
				{
					conn.Dispose();
				}
			}
		}
		#endregion

		#region Delete
		public override int Delete(Customer customer, SqlConnection conn, SqlTransaction trans)
		{
			bool destroyConn = false;

			if ( conn == null )
			{
				destroyConn = true;
				conn = new SqlConnection(_connectionString);
			}

			try
			{
				SqlCommand cmd = new SqlCommand("daltest_Customer_Delete", conn);
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Transaction = trans;

				if ( conn.State != ConnectionState.Open )
				{
					conn.Open();
				}

				cmd.Parameters.Add("@CustomerID", SqlDbType.Int).Value = customer.Key.CustomerID;

				// First, execute base class delete
				int rows = cmd.ExecuteNonQuery();

				// Then delete subclass
				DbUser dbUser = new DbUser(_connectionStringName);
				dbUser.Delete(customer, conn, trans);

				customer._UpdateState(ObjectState.Deleted);

				return rows;
			}
			finally
			{
				if ( destroyConn && conn.State == ConnectionState.Open )
				{
					conn.Close();
				}

				if ( destroyConn )
				{
					conn.Dispose();
				}
			}
		}
		#endregion
	}
}
